package com.kingsoft.dc.khaos.module.mpp.client;

import com.kingsoft.dc.khaos.module.mpp.exception.MppSqlException;
import com.kingsoft.dc.khaos.module.mpp.model.ColumnEntity;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.DatabaseMetaData;
import java.util.ArrayList;
import java.util.List;

import static java.sql.DriverManager.getConnection;

/**
 * mpp 操作session.
 */
public class MppSession {
    /**
     * 连接数据库信息
     */
    private Connect connect;
    /**
     * 数据库连接
     */
    private Connection jdbcConnection;

    public Connect getConnect() {
        return connect;
    }

    public void setConnect(Connect connect) {
        this.connect = connect;
    }

    public Connection getJdbcConnection() {
        return jdbcConnection;
    }

    private void setJdbcConnection(Connection jdbcConnection) {
        this.jdbcConnection = jdbcConnection;
    }

    public void initConnection(Connect connect) {
        System.out.println("开始获取jdbcConnect");
        this.connect = connect;
        try {
            Class.forName("org.postgresql.Driver");
            String url = String.format("jdbc:postgresql://%s:%s/%s", this.connect.getHost(), this.connect.getPort(), this.connect.getInstanceName());
            String userName = connect.getUsername();
            String password = connect.getPassword();
            jdbcConnection = getConnection(url, userName, password);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
            throw new MppSqlException("执行MPP SQL异常.");
        }
        System.out.println("成功获取jdbcConnect");
    }

    public Boolean executeOneSql(String sql) {
        String formatSql = sql.replace('\r', ' ').replace('\n', ' ').replaceAll(" {2,}+", " ").replaceAll(";", ";\n");
        System.out.println(">>>>待执行SQL如下");
        System.out.println(formatSql);
        Statement statement = null;
        Boolean executeResult = false;
        try {
            jdbcConnection.setAutoCommit(false);
            System.out.println(">>>>SQL开始执行");
            statement = jdbcConnection.createStatement();
            executeResult = statement.execute(sql);
            jdbcConnection.commit();
            return executeResult;
        } catch (SQLException e) {
            e.printStackTrace();
            try {
                if (jdbcConnection != null) {
                    jdbcConnection.rollback();
                }
            } catch (SQLException e1) {
                e1.printStackTrace();
                System.out.println("rollback fail.");
            }
            executeResult = false;
        } finally {
            if (statement != null) {
                try {
                    statement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            System.out.println(">>>>SQL执行结束");
        }
        if (!executeResult) {
            throw new MppSqlException("mpp sql 执行异常.");
        } else {
            return executeResult;
        }
    }

    public long executeQuery(String sql) {
        String formatSql = sql.replace('\r', ' ').replace('\n', ' ').replaceAll(" {2,}+", " ").replaceAll(";", ";\n");
        System.out.println(">>>>待执行SQL如下");
        System.out.println(formatSql);
        Statement statement = null;
        long count = 0l;
        try {
            System.out.println(">>>>SQL开始执行");
            statement = jdbcConnection.createStatement();
            ResultSet resultSet = statement.executeQuery(sql);

            while (resultSet.next()) {
                count = resultSet.getLong(1);
            }

            resultSet.close();
            return count;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (statement != null) {
                try {
                    statement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            System.out.println(">>>>SQL执行结束");
        }
        return 0l;
    }

    public long count(String sql) {
        long number = -1;
//        ResultSet resultSet = executeQuery(sql);
//        try {
//            while (resultSet.next()) {
//                number = resultSet.getInt(1);
//            }
//        } catch (SQLException e) {
//            e.printStackTrace();
//            throw new MppSqlException("mpp sql 执行异常.");
//        }
        number = executeQuery(sql);
        return number;

    }

    public void executeBatchSqls(List<String> sqls) {
        Statement statement = null;
        try {
            System.out.println(">>>>SQL开始执行");
            statement = jdbcConnection.createStatement();
            for (String sql : sqls) {
                statement.addBatch(sql);
            }
            statement.executeBatch();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (statement != null) {
                try {
                    statement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                    //throw new MppSqlException("mpp sql 执行异常.");
                }
            }
            System.out.println(">>>>SQL执行结束");
        }
    }

    public List<ColumnEntity> getTableColumns(String schemaName, String tableName) {
        System.out.println("开始获取字段元数据信息");
        String fullName = String.format("%s.%s", schemaName, tableName);
        List<ColumnEntity> cols = new ArrayList<ColumnEntity>();
        DatabaseMetaData dbmd = null;
        try {
            dbmd = jdbcConnection.getMetaData();
            // 转义 _和% 通配符
            System.out.println("添加转义符");
            String escape = dbmd.getSearchStringEscape();
            schemaName = schemaName.replace("_", escape + "_").replace("%", escape + "%");
            tableName = tableName.replace("_", escape + "_").replace("%", escape + "%");
            System.out.println("转义符添加完成:schemaName:" + schemaName + ", tableName:" + tableName);
            ResultSet rs = dbmd.getColumns(jdbcConnection.getCatalog(), schemaName, tableName, null);
            while (rs.next()) {
                ColumnEntity col = new ColumnEntity(String.format("\"%s\"", rs.getString("COLUMN_NAME")), rs.getString("TYPE_NAME"), rs.getInt("COLUMN_SIZE"), rs.getInt("DECIMAL_DIGITS"));
                cols.add(col);
            }
        } catch (SQLException e) {
            e.printStackTrace();
            throw new MppSqlException("获取表" + schemaName + "." + tableName + "列名失败");
        }
        System.out.println("成功获取字段元数据信息");
        return cols;
    }

    public void close() {
        if (this.getJdbcConnection() != null) {
            System.out.println("closing connect...");
            try {
                this.getJdbcConnection().close();
                System.out.println("closed connect...");
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}
